package com.amado.course45mysql02.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;

import com.amado.course45mysql02.domain.Student;

public class EmployeeAddDao {
	// 使用拼sql增加
	public void add1(Student student) {
		Connection conn = DBConnection.getConn();
		// delete from t_user;
		// zhangshan   'zhangshan or 1=1
		//拼接SQL
		String sql = "insert into t_student (name,sex,phone,age) values('" + student.getName() + "'," + student.getSex()
				+ ",'" + student.getPhone() + "'," + student.getAge() + ")";
		System.out.println("sql = "+sql);
		
		//执行静态sql的对象，如果强行静态SQL对象处理动态的SQL会存在SQL注入
		Statement ps = null;
		try {
			//执行SQL
			ps = conn.createStatement();
			System.out.println(ps.execute(sql));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		//关闭连接
		DBConnection.close(null, ps, conn);
	}

	// 使用替换变量增加
	public void add2(Student student) {
		Connection conn = DBConnection.getConn(); 
		String sql = "insert into t_student(name,sex,phone,age)values(?,?,?,?)";
		//执行一个动态的SQL对象,他会对关联字做转义，防止 SQL注入
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			//delete from t_user; `delete`转义符
			ps.setString(1, student.getName());
			ps.setByte(2, student.getSex());
			ps.setString(3, student.getPhone());
			ps.setShort(4, student.getAge());
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBConnection.close(null, ps, conn);
	}
	/**
	 * 批量插入
	 * @param studentList
	 */
	public void addBatch(List<Student> studentList) {
		Connection conn = DBConnection.getConn();
		String sql = "insert into t_student(name,sex,phone,age)values(?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			for (Student student : studentList) {
				ps.setString(1, student.getName());
				ps.setByte(2, student.getSex());
				ps.setString(3, student.getPhone());
				ps.setShort(4, student.getAge());
				ps.addBatch();
			}
			ps.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBConnection.close(null, ps, conn);
	}

	public static void main(String[] args) {
		EmployeeAddDao employeeDao = new EmployeeAddDao();
		employeeDao.add1(new Student("张三1", (byte) 1, "18578223324", (short) 20));
		employeeDao.add2(new Student("李四", (byte) 1, "18578223322", (short) 20));
		employeeDao.addBatch(Arrays.asList(new Student("李四baa", (byte) 1, "18578223322", (short) 20),new Student("李四cc", (byte) 1, "18578223322", (short) 20)));
	}
}
